<?php

namespace app\index\controller;

use app\common\controller\Frontend;
use think\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Filesystem;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Index extends Frontend
{

    protected $noNeedLogin = '*';
    protected $noNeedRight = '*';
    protected $layout = '';



    public function index(){

        header('location:/myadmin.php');
        echo date('Y-m-d');
        die;
    }
    /**
     * 删除不需要的数据内容
     */
    public function deldata(){
        Db::name('aa_case')->where('id', 'gt',0)->delete();
        Db::name('aa_case_file')->where('id', 'gt',0)->delete();
        Db::name('aa_case_live')->where('id', 'gt',0)->delete();

        Db::name('aa_dongtai')->where('id', 'gt',0)->delete();

        Db::name('aa_kuaidi')->where('id', 'gt',0)->delete();
        Db::name('aa_kuaidi_log')->where('id', 'gt',0)->delete();

        Db::name('aa_note')->where('id', 'gt',0)->delete();
        Db::name('aa_note_back')->where('id', 'gt',0)->delete();


        Db::name('aa_photolpg_log')->where('id', 'gt',0)->delete();
        Db::name('aa_kuaidi_log')->where('id', 'gt',0)->delete();

        Db::name('newphoto')->where('id', 'gt',0)->delete();

        Db::name('quotation')->where('id', 'gt',0)->delete();
        Db::name('quotaion_log')->where('id', 'gt',0)->delete();



    }


    /**
        读取文件里的内容到  数据库里
     */
    public function seeexceltosql($id=668){


        $case=Db::name('aa_case')->alias('a')
            ->join('cmf_quotation b','a.q_id=b.id')
            ->where(['a.id'=>$id])
            ->field('a.*,b.order,b.id as quotation_id')->find();

        $list=Db::name('quotaion_log')->alias('a')
            ->join('cmf_catefrolist b','a.catefrolist_id=b.id')
            ->where(['a.quotation_id'=>$case['quotation_id']])
            ->field('a.*,b.name')
            ->select();


//        print_r($list);


        // 假设要读取的 Excel 文件路径
        $filePath = './upload/excel/2025-04-17250417_CHAMPION_优先.xlsx';


            // 加载 Excel 文件
            $spreadsheet = IOFactory::load($filePath);
            // 获取活动工作表
            $sheet = $spreadsheet->getActiveSheet();
            // 获取工作表的最大行数
           $highestRow = $sheet->getHighestRow();
            // 初始化变量
        $highestColumnWithValues = 'A'; // 假设至少有 A 列有值

        // 遍历每一列，从 A 列开始

        $counta=0;
        for ($col = 'A'; ; $col++) {
            $hasValue = false; // 标记当前列是否有值
            $row = 4;
            for ($row = 4; $row <= 4; $row++) {
                $cellValue = $sheet->getCell($col . $row)->getValue();
                if ($cellValue !== null && trim($cellValue) !== '') { // 检查是否有值
                    $hasValue = true;
                    break;
                }
            }
            if (!$hasValue) {
                break; // 如果当前列没有值，退出循环
            }

            $counta++;
            $highestColumnWithValues = $col; // 更新有值的最高列
        }

        // 从第 3 行开始获取数据到数组
        $data = [];

        $rowData = [];

        $row = 3;
        //除开通用参数
        for ($col = 12; $col <= $counta; $col++) {
            $cell = $sheet->getCellByColumnAndRow($col, $row);
            $value = $cell->getValue();
            if ($value instanceof \PhpOffice\PhpSpreadsheet\RichText\RichText) {
                $value = $value->getPlainText();
            }
            $rowData[] = $value;
        }
        $data[] = $rowData;

        $arrays=[];
        foreach ($list as $li) {
            $array=[];

            $array['title']=$li['catefrolist_name'];
            $array['size']=$li['size'];
            $array['status']=0;
            $array['set']='';
            $array['money']=$li['money'];
            $array['type']=$li['type'];
            $array['id']=$li['id'];
            $arrays[]=$array;

        }


//        print_r($arrays);
//        echo "---------";
        // 输出读取的数据（可根据需求处理数据）

        $rowDatas=[];
        $allmoney=0;
        for ($row = 5; $row <= $highestRow; $row++){
            $rowData = [];
            $rowData=[];
            $more=[];

            for ($col = 1; $col <= $counta; $col++) {
                $cell = $sheet->getCellByColumnAndRow($col, $row);
                $value = $cell->getValue();

                if ($value instanceof \PhpOffice\PhpSpreadsheet\RichText\RichText) {
                    $value = $value->getPlainText();
                }

                if ($col<12){
                    $rowData[] = $value;
                }else{
                    $coun=$col-12;
                    $arrays[$coun]['set']=$value;
                    if  ($value == '是'){
                        $allmoney+=$arrays[$coun]['money'];
                    }

                    $more[]=$arrays[$coun];
                }

            }

            $rowData['more']=$more;

            $rowDatas[]=$rowData;

        }
        //最后的数据；  搭配
        //print_r($rowDatas);

        foreach ($rowDatas as $aa=>$array) {
            $adddata=[];
            $adddata['caseid']=$id;
            $adddata['title']=$array['0'];
            $adddata['code']=$array[1];
            $adddata['spu']=$array[2];
            $adddata['sku']=$array[3];
            $adddata['title_b']=$array[4];
            $adddata['color1']=$array[5];
            $adddata['color2']=$array[6];
            $adddata['size']=$array[7];
            $adddata['sex']=$array[8];
            $adddata['colorpro']=$array[9];
            $adddata['desc']=$array[10];
            $adddata['more']=serialize($array['more']);
            $adddata['createtime']=time();
            $adddata['updatetime']=time();

            Db::name('newphoto')->insert($adddata);

        }

        db('aa_case')->where(['id'=>$id])->setInc('money',$allmoney);

}






    public function getcasesexcel(){

        $id=669;

        $case=Db::name('aa_case')->alias('a')
            ->join('cmf_quotation b','a.q_id=b.id')
            ->where(['a.id'=>$id])
            ->field('a.*,b.order,b.id as quotation_id')->find();


//        if($case['files']){
//            $this->success('文件地址', $case['files']);
//        }

        $list=Db::name('quotaion_log')->alias('a')
            ->join('cmf_catefrolist b','a.catefrolist_id=b.id')
            ->where(['a.quotation_id'=>$case['quotation_id'],'a.seesyatus'=>1])
            ->field('a.*,b.name')
            ->select();

        // 假设这里指定要修改的文件路径
        $fileToModify = "./upload/excel/demo.xlsx";

        // 加载已有的 Excel 文件
        $spreadsheet = IOFactory::load($fileToModify);
        $sheet = $spreadsheet->getActiveSheet();



        $headers = [];
        // 追加 $list 里的 name 值到 $headers 数组
        foreach ($list as $v) {
            $headers[] = '是/否';
        }

        $column = 'L';
        foreach ($headers as $header) {
            $sheet->setCellValue($column.'3', $header);
            $column++;
        }

        // 示例：修改表头
        $headers = [];
        // 追加 $list 里的 name 值到 $headers 数组
        foreach ($list as $v) {
            $headers[] = $v['name'];
        }

        $column = 'L';
        foreach ($headers as $header) {
            $sheet->setCellValue($column.'4', $header);
            $column++;
        }

        // 创建 Xlsx 写入器
        $writer = new Xlsx($spreadsheet);
        $savePath = "/upload/excel/";

        // 如果需要保存为新文件，可以指定新的文件名
        $newFileName = date('Y-m-d').$case['title'].'.xlsx';
        $newFullPath = '.'.$savePath . $newFileName;
        $writer->save($newFullPath);
        // ... existing code ...

        $file= cdnurl($savePath.$newFileName,true);
        Db::name('aa_case')->where(['id'=>$id])->update(['files'=>$file]);

        echo $file;

    }



}
